﻿#region Imports
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Media;
using System.Text;
using System.Windows.Forms;
using System.Windows.Input;
using System.Windows.Threading;
using CommonUtilities.Attributes;
using CommonUtilities.Commands;
using CommonUtilities.Enums;
using CommonUtilities.Extensions;
using CommonUtilities.Info;
using CommonUtilities.Messages;
using CommonUtilities.Util;
using EntertainmentManager.Models;
using EntertainmentManager.Properties;
#endregion

namespace EntertainmentManager.ViewModels
{
    [DefaultValue(Episode)]
    public enum eDisplayGroup
    {
        /// <summary>
        /// Series
        /// </summary>
        Series,
        /// <summary>
        /// Season
        /// </summary>
        Season,
        /// <summary>
        /// Episode
        /// </summary>
        Episode,
        /// <summary>
        /// Hierarchical
        /// </summary>
        Hierarchical,
        /// <summary>
        /// Summary
        /// </summary>
        Summary
    }

    public class MainViewModel : BaseViewModel
    {
        public Type displayGroupType;
        public ObservableCollection<string> validHeaders = new ObservableCollection<string>();
        public ObservableCollection<string> missingCellTemplates = new ObservableCollection<string>();
        public Dictionary<string, int> dictColumnsSorted = new Dictionary<string, int>();
        public Dictionary<string, string> dictPropertyHeaderDisplayMap = new Dictionary<string, string>();
        public Dictionary<string, string> dictPropertyDescriptionMap = new Dictionary<string, string>();
        public Dictionary<string, int> dictPropertySortIndexMap = new Dictionary<string, int>();
        public Dictionary<string, string> dictPropertyCellTemplateMap = new Dictionary<string, string>()
            //.AddToDictionary("SeriesTitle", "coltempSeriesTitle")
            .AddToDictionary("SeasonIndex", "coltempSeasonIndex")
            .AddToDictionary("EpisodeIndex", "coltempEpisodeIndex")
            //.AddToDictionary("EpisodeTitle", "coltempEpisodeTitle")
            .AddToDictionary("ImdbUrl", "coltempImdbUrl")
            .AddToDictionary("FullPath", "coltempFullPath")
            .AddToDictionary("ActuallPath", "coltempActuallPath")
            //.AddToDictionary("FileExtn", "coltempFileExtn")
            //.AddToDictionary("FileName", "coltempFileName")
            .AddToDictionary("OriginalAirDate", "coltempOrgAirDate")
            //.AddToDictionary("Sort", "coltempSort")
            .AddToDictionary("Description", "coltempDescription")
            .AddToDictionary("FileExists", "coltempFileExists");

        /// <summary>
        /// Stores the last property to have set DisplayIndex
        /// </summary>
        public string nameSortLast = "";

        int minSeasonPadLength = 2;
        int minEpisodePadLength = 2;

        private string defaultNone = "None";

        private string defaultAll = "All";
        public string DefaultAll
        {
            get { return defaultAll; }
        }

        private string appTitle;
        public string AppTitle
        {
            get { return appTitle; }
            set { appTitle = value; RaisePropertyChanged("AppTitle"); }
        }

        private string appStatus;
        public string AppStatus
        {
            get { return appStatus; }
            set
            {
                appStatus = value;
                RaisePropertyChanged("AppStatus");

                if (!appStatus.IsBlank()) AllAppStatus.AddItem(appStatus);
            }
        }

        private ObservableCollection<string> allAppStatus;
        public ObservableCollection<string> AllAppStatus
        {
            get { return allAppStatus; }
            set { allAppStatus = value; RaisePropertyChanged("AllAppStatus"); }
        }

        private string loadStatistics;
        public string LoadStatistics
        {
            get { return loadStatistics; }
            set { loadStatistics = value; RaisePropertyChanged("LoadStatistics"); }
        }

        private ObservableCollection<EpisodeInfo> allEpisodeInfo;
        public ObservableCollection<EpisodeInfo> AllEpisodeInfo
        {
            get { return allEpisodeInfo; }
            set
            {
                ObservableCollection<EpisodeInfo> tmp = value;
                int minSeasonPadLength = tmp._Select(ep => Convert.ToString(ep.SeasonIndex).Length).Max();
                int minEpisodePadLength = tmp._Select(ep => Convert.ToString(ep.EpisodeIndex).Length).Max();

                // RKD TESTING
                tmp = tmp._Where(ep => (new string[] { "Alfred Hitchcock Hour, the", "Blue Planet, the", "TaleSpin", "Planet Earth", "Game of Thrones", "Alfred Hitchcock Presents" })
                    .Contains(ep.SeriesTitle)).ToObservableCollection();

                // Update data sort index and pad season/episode indices
                tmp.AddCollectionIndex("Sort");
                tmp._ForEach(ep =>
                {
                    ep.UpdatePadLength(minSeasonPadLength, minEpisodePadLength);
                });

                // RKD : Update years

                allEpisodeInfo = tmp._OrderBy(ep => ep.SeriesTitle)._ThenBy(ep => ep.SeasonIndex)._ThenBy(ep => ep.EpisodeIndex).ToObservableCollection();
                RaisePropertyChanged("AllEpisodeInfo");

                // Save to XML
                WriteInfoXml<EpisodeInfo, EpisodeInfoMirror>(allEpisodeInfo);

                LoadStatistics = "Total episodes: {0}".FormatTextFromParams(allEpisodeInfo.GetCount());
            }
        }

        private ObservableCollection<SeasonInfo> allSeasonInfo;
        public ObservableCollection<SeasonInfo> AllSeasonInfo
        {
            get { return allSeasonInfo; }
            set { allSeasonInfo = value.AddCollectionIndex("Sort").ToObservableCollection(); RaisePropertyChanged("AllSeasonInfo"); }
        }

        public ObservableCollection<SeriesInfo> AllSeriesBasePath { get; set; }

        private ObservableCollection<SeriesInfo> allSeriesInfo;
        public ObservableCollection<SeriesInfo> AllSeriesInfo
        {
            get { return allSeriesInfo; }
            set
            {
                allSeriesInfo = value.AddCollectionIndex("Sort").ToObservableCollection();
                RaisePropertyChanged("AllSeriesInfo");

                // Save to XML
                WriteInfoXml<SeriesInfo, SeriesInfoMirror>(allSeriesInfo);
            }
        }

        private Dictionary<string, string> allFilters;
        public Dictionary<string, string> AllFilters
        {
            get { return allFilters; }
            set
            {
                allFilters = value;
                RaisePropertyChanged("AllFilters");

                IsFilterEnabled = allFilters.GetCount() > 0;
            }
        }

        private ObservableCollection<string> allFiltersValues;
        public ObservableCollection<string> AllFilterValues
        {
            get { return allFiltersValues; }
            set { allFiltersValues = value; RaisePropertyChanged("AllFilterValues"); }
        }

        private ObservableCollection<string> allSeriesFilter;
        public ObservableCollection<string> AllSeriesFilter
        {
            get { return allSeriesFilter; }
            set { allSeriesFilter = value; RaisePropertyChanged("AllSeriesFilter"); }
        }

        private eDisplayGroup selectedDisplayGroup;
        public eDisplayGroup SelectedDisplayGroup
        {
            get { return selectedDisplayGroup; }
            set
            {
                selectedDisplayGroup = value;
                RaisePropertyChanged("SelectedDisplayGroup");

                Dispatcher.CurrentDispatcher.BeginInvoke(new Action(() =>
                    {
                        string mode = selectedDisplayGroup.ToString().ToLower() + "s";
                        DateTime start = DateTime.Now;

                        UniversalDataSource = new ObservableCollection<object>();
                        dictColumnsSorted.Clear();

                        switch (selectedDisplayGroup)
                        {
                            case eDisplayGroup.Hierarchical:
                                break;

                            case eDisplayGroup.Summary:
                                try { throw new NotImplementedException("YET TO BE IMPLEMENTED.."); } // RKD
                                catch (Exception ex) { MessageBoxUtil.Show(true, ex.StackTrace, ex.Message); }
                                break;

                            case eDisplayGroup.Series:
                                OnDisplayGroupSelection<SeriesInfo>(AllSeriesInfo, 1);
                                mode = selectedDisplayGroup.ToString().ToLower();
                                break;

                            case eDisplayGroup.Season:
                                OnDisplayGroupSelection<SeasonInfo>(AllSeasonInfo, 2);
                                break;

                            case eDisplayGroup.Episode:
                            default:
                                OnDisplayGroupSelection<EpisodeInfo>(AllEpisodeInfo, 3);
                                break;
                        }

                        PopulateFilters();

                        DateTime end = DateTime.Now;
                        DisplayStatus(start, end, "Display group change", "Hierarchy..");
                        LoadStatistics = "Total {0}: {1}".FormatTextFromParams(mode, UniversalDataSource.GetCount());
                    }));
            }
        }

        private string selectedFilter;
        public string SelectedFilter
        {
            get { return selectedFilter; }
            set
            {
                selectedFilter = value;
                RaisePropertyChanged("SelectedFilter");

                PopulateFilterValues();
            }
        }

        private string selectedSeries;
        public string SelectedSeries
        {
            get { return selectedSeries; }
            set { selectedSeries = value; RaisePropertyChanged("SelectedSeries"); }
        }

        private ObservableCollection<object> universalDataSource;
        public ObservableCollection<object> UniversalDataSource
        {
            get { return universalDataSource; }
            set { universalDataSource = value; RaisePropertyChanged("UniversalDataSource"); }
        }

        private int frozenColumnCount;
        public int FrozenColumnCount
        {
            get { return frozenColumnCount; }
            set { frozenColumnCount = value; RaisePropertyChanged("FrozenColumnCount"); }
        }

        private bool isFilterEnabled;
        public bool IsFilterEnabled
        {
            get { return isFilterEnabled; }
            set { isFilterEnabled = value; RaisePropertyChanged("IsFilterEnabled"); }
        }

        /// <summary>
        /// Sortable fields
        /// </summary>
        public ObservableCollection<string> PropertyFieldsSortable { get; set; }

        public Dictionary<string, string> SeriesPathMap { get; set; }

        public Dictionary<string, string> AllFileExtnMap { get; set; }

        public string ApplicationPath { get { return AppDomain.CurrentDomain.BaseDirectory; } }
        // return Process.GetCurrentProcess().MainModule.FileName.GetDirectoryNameFull(); } } // System.AppDomain.CurrentDomain.BaseDirectory

        public ICommand OpenLinkCommand { get; private set; }
        public ICommand FilterDataCommand { get; private set; }
        public ICommand DisplayGroupCommand { get; private set; }
        public ICommand ToggleAllSeriesStateCommand { get; private set; }
        public ICommand ToggleAllSeasonStateCommand { get; private set; }
        public ICommand FiltersUpdatedCommand { get; private set; }

        public MainViewModel()
        {
            FilterDataCommand = new RelayCommand<object>((parameter) => { if (parameter is string) MessageBox.Show(parameter as string, "Filter.."); });
            DisplayGroupCommand = new RelayCommand<eDisplayGroup>((parameter) => { SelectedDisplayGroup = parameter; /*Enum.GetValues(typeof(eDisplayGroup)).Cast<eDisplayGroup>().Where(e => e.ToString() == parameter).First();*/ });
            ToggleAllSeriesStateCommand = new RelayCommand<bool>((parameter) => { AllSeriesInfo._ForEach(rec => rec.IsExpanded = parameter); });
            ToggleAllSeasonStateCommand = new RelayCommand<bool>((parameter) => { AllSeriesInfo._ForEach(rec => rec.Seasons._ForEach(s => s.IsExpanded = parameter)); });
            FiltersUpdatedCommand = new RelayCommand<ComboBox>((parameter) => { parameter.Enabled = (parameter.Items.Count > 0); });
            OpenLinkCommand = new RelayCommand<object>((parameter) =>
            {
                if (parameter is string)
                {
                    if (parameter.ToString().Contains(@"://") || (parameter.ToString().Contains(@":\") && (parameter.ToString().ValidatePath(ePathValidation.Directory) || parameter.ToString().ValidatePath(ePathValidation.File))))
                        Process.Start(parameter as string);
                    else if (parameter.ToString().Contains(@":\")) { MessageBoxUtil.Show(true, "Path does not exist."); Clipboard.SetText(parameter as string); }
                }
            });

            AppTitle = AppInfo.AssemblyTitle;

            try { ReadSeriesPathMap(ApplicationPath.AppendPath("TV_series_base.txt")); } // RKD - debug
            catch (Exception ex) { MessageBoxUtil.Show(Debugger.IsAttached, ex.StackTrace, ex.Message); }
        }

        private void ReadSeriesPathMap(string sSeriesBasePath)
        {
            Dispatcher.CurrentDispatcher.Invoke(new Action(() =>
                {
                    DateTime start = DateTime.Now;
                    Dictionary<string, string> dictDataImportName = AttributeLists<SeriesInfo>.PropertyMapDataImportName;

                    if (sSeriesBasePath.ValidatePath(ePathValidation.File))
                    {
                        // Read from path
                        AllSeriesBasePath = Utilities.ReadCSV<SeriesInfo>(sSeriesBasePath, dictDataImportName, tableName: "SeriesPathMap").ToObservableCollection();

                        SeriesPathMap = new Dictionary<string, string>();
                        SeriesPathMap = AllSeriesBasePath._Select(srs => new { Key = srs.SeriesTitle, Value = srs.FullPath.Trim() })
                            .ToDictionary(dict => dict.Key, dict => dict.Value);

                        GenerateFileExtnMap();
                    }

                    ReadEpisodes(start);
                }));
        }

        private void GenerateFileExtnMap()
        {
            string[] invalidExtn = "SRT,SUB,IDX,INI,ICO,MPCPL,DB,JPG,PNG,BMP".Split(',');
            string[] validExtn = "AVI,MKV,MP4,DAT,VOB,M4V,FLV,MPG".Split(',');

            Dictionary<string, string> allFileExtnMap = new Dictionary<string, string>();
            ObservableCollection<string> allSeriesBase = new ObservableCollection<string>();

            DateTime start = DateTime.Now;
            allSeriesBase = SeriesPathMap.Values._Select(p => p.GetDirectoryUp(1)).Distinct().ToObservableCollection();
            allSeriesBase._ForEach(path =>
               {
                   if (path.ValidatePath(ePathValidation.Directory))
                   {
                       /*AllFileExtnMap =*/
                       Directory.GetFiles(path, "*.*", SearchOption.AllDirectories)
                           ._Where(p => validExtn._Contains(Path.GetExtension(p).RemoveExtnChars().ToUpper()))
                           ._ForEach(p =>
                           {
                               string epPath = Path.GetDirectoryName(p).AppendPath(Path.GetFileNameWithoutExtension(p));
                               if (!allFileExtnMap.ContainsKey(epPath))
                                   allFileExtnMap.Add(epPath, Path.GetExtension(p).RemoveExtnChars().ToUpper());
                           });
                   }
               });
            MessageBox.Show((DateTime.Now - start).FormatTimeSpan("ss.fff"), "RKD : GenerateFileExtnMap");

            // Clipboard.SetText(allFileExtnMap.Values.Distinct().AppendText(",")); -- RKD debug
            AllFileExtnMap = allFileExtnMap;
        }

        private void ReadEpisodes(DateTime start)
        {
            ObservableCollection<EpisodeInfo> allEpisodes = new ObservableCollection<EpisodeInfo>();
            Dispatcher.CurrentDispatcher.BeginInvoke(new Action(() =>
                {
                    #region Load episodes

                    Dictionary<string, string> dictDataImportName = AttributeLists<EpisodeInfo>.PropertyMapDataImportName;
                    List<List<object>> items = new List<List<object>>();

                    OpenFileDialog openFileDialog = new OpenFileDialog();
                    openFileDialog.FileName = "*.xlsx";
                    openFileDialog.CheckFileExists = true;
                    openFileDialog.CheckPathExists = true;
                    openFileDialog.DefaultExt = "*.xlsx";
                    openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|Excel 97-2003 files (*.xls)|*.xls|Text files (*.txt)|*.txt";
                    openFileDialog.InitialDirectory = Environment.SpecialFolder.MyDocuments.GetFolderPath();
                    openFileDialog.Multiselect = false;
                    openFileDialog.RestoreDirectory = true;
                    openFileDialog.Title = "Choose file..";
                    openFileDialog.ValidateNames = true;

                    openFileDialog.FileName = @"E:\TV_series_2.xlsx"; // RKD : TEST
                    openFileDialog.FileName = @"E:\TV_series_old.txt"; // RKD : TEST
                    openFileDialog.FileName = ApplicationPath.AppendPath("TV_series.txt"); // RKD : TEST
                    // RKD                                           if (openFileDialog.ShowDialog() != DialogResult.OK) return; // KEEP AFTER TESTING
                    bool isExcel = "xls|xlsx".Split('|')._Contains(Path.GetExtension(openFileDialog.FileName).RemoveExtnChars());
                    bool isText = (Path.GetExtension(openFileDialog.FileName).RemoveExtnChars() == "txt");

                    // Open file
                    Microsoft.Office.Interop.Excel.Application ExcelObj = null;
                    if (isExcel)
                    {
                        ExcelObj = new Microsoft.Office.Interop.Excel.Application();

                        if (ExcelObj == null)
                        {
                            MessageBox.Show("ERROR: EXCEL couldn't be started!");
                            System.Windows.Forms.Application.Exit();
                        }
                    }
                    else if (isText) ; // No need to open separately

                    if (isExcel)
                    {
                        try
                        {
                            Microsoft.Office.Interop.Excel.Workbook theWorkbook = null;
                            Microsoft.Office.Interop.Excel.Sheets sheets = null;
                            Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
                            List<string> lines = new List<string>();

                            // Read file and populate
                            if (isExcel)
                            {
                                //System.Diagnostics.Process.GetProcesses()._Where(proc => proc.ProcessName.ToLower() == "excel").ForEach(proc => proc.Kill());
                                if (Process.GetProcesses().Any(proc => proc.ProcessName.ToLower() == "excel"))
                                    MessageBoxUtil.Show(Debugger.IsAttached, "EXCEL is open. Close all windows to proceed.");

                                theWorkbook = ExcelObj.Workbooks.Open(openFileDialog.FileName);
                                sheets = theWorkbook.Worksheets;
                                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("EP_list"); // .Item[];
                            }

                            try
                            {
                                // Get no. of columns to update
                                int iColMax = 1;
                                long iRowMax = 1;

                                if (isExcel)
                                {
                                    do { iColMax++; }
                                    while (worksheet.Application.Cells[1, iColMax].Value != null);

                                    // Get no. of rows to update
                                    do { iRowMax++; }
                                    while (worksheet.Application.Cells[iRowMax, 1].Value != null);
                                }
                                else if (isText)
                                {
                                    iColMax = (int)lines[0].Split('\t').GetCount();
                                    iRowMax = lines.GetCount();
                                }

                                // Iterate records
                                for (int iRow = 1; iRow <= iRowMax; iRow++) // rows
                                    // RKD HERE_SOMETHING_IS_WRONG_AS_SHEET2_OPENS_EVEN_IF_SELECT_IS_DONE;
                                    // RKD MOVE_TO_COMM_UTIL
                                    // RKD USE_NEW_THREAD_FOR_BACKGROUNG_PROCESSING

                                    items.Add(isExcel ? IterateColumnsValues(worksheet, iColMax, iRow)
                                        : lines.ElementAt(iRow - 1).Split('\t').Cast<object>().ToList());

                                SystemSounds.Asterisk.Play();
                            }
                            catch (Exception) { SystemSounds.Hand.Play(); }
                            finally
                            {
                                if (isExcel) theWorkbook.Close();
                                theWorkbook = null;
                            }

                            SystemSounds.Asterisk.Play();
                        }
                        catch (Exception) { SystemSounds.Hand.Play(); }
                        finally { ExcelObj = null; }

                        List<object> header = items.First();
                        items.Where(itm => itm != header).ForEach(itm => allEpisodes.Add(new EpisodeInfo(header, itm, dictDataImportName)));
                    }
                    else if (isText)
                        allEpisodes = Utilities.ReadCSV<EpisodeInfo>(openFileDialog.FileName, dictDataImportName, '\t', "EpisodeInfo").ToObservableCollection();

                    // Format properties
                    DateTime start1 = DateTime.Now;
                    ObservableCollection<EpisodeInfo> temp = allEpisodes.Join(AllFileExtnMap, ep => ep.FileName.ToLower(),
                        dict => Path.GetFileNameWithoutExtension(dict.Key).ToLower(),
                        (ep, dict) => new EpisodeInfo { EpisodeTitle = ep.EpisodeTitle, FileExtn = dict.Value, ActualPath = dict.Key + "." + dict.Value }).ToObservableCollection();

                    // allEpisodes._ForEach(ep => { if (ep.FileExtn.IsBlank()) ep.FileExtn = AllFileExtnMap.GetValueFromKey(ep.FileName.WordAt(0), true); }); // Check with s00e00 format
                    // allEpisodes._ForEach(ep => { if (ep.FileExtn.IsBlank()) ep.FileExtn = AllFileExtnMap.GetValueFromKey(ep.FileName, true).Coalesce().Replace(": Part ", " -part"); }); // Check for split episodes
                    // RKD allEpisodes._ForEach(ep => { if (ep.FileExtn.IsBlank()) ep.FileExtn = AllFileExtnMap.GetValueFromKey(ep.FileName).Coalesce().ToUpper(); }); // Check for combined episodes
                    MessageBox.Show((DateTime.Now - start1).FormatTimeSpan("ss.fff"), "RKD : ReadEpisodes");

                    // Populate properties
                    AllEpisodeInfo = allEpisodes;
                    PopulateSeasonInfo();
                    PopulateSeriesInfo();

                    // Filter by series
                    PopulateSeriesFilter();
                    #endregion Load episodes

                    // RKD SelectedDisplayGroup = eDisplayGroup.Episode; -- try

                    // Load summary
                    DateTime end = DateTime.Now;
                    DisplayStatus(start, end, "Episode load", "Episodes..");
                }));
        }

        private void PopulateSeriesFilter()
        {
            AllSeriesFilter = AllSeriesInfo.Select(ep => ep.SeriesTitle).AddDefaultItemAndSort(DefaultAll);
            SelectedSeries = AllSeriesFilter.FirstOrDefault();
        }

        private void OnDisplayGroupSelection<T>(IEnumerable<object> info, int frozenColumnCount)
            where T : class, new()
        {
            displayGroupType = typeof(T);
            FrozenColumnCount = frozenColumnCount;
            validHeaders = AttributeLists<T>.PropertyFieldsBrowsable; // .GeneratePropertyList("BrowsableAttribute", "Browsable", BrowsableAttribute.Default.Browsable);
            dictPropertyHeaderDisplayMap = AttributeLists<T>.PropertyMapDisplayName;
            dictPropertyDescriptionMap = AttributeLists<T>.PropertyMapDescription;
            PropertyFieldsSortable = AttributeLists<T>.PropertyFieldsSortable;
            dictPropertySortIndexMap = AttributeLists<T>.PropertyMapSortIndex;

            // RKD DEBUG (create cell telplates at run-time with new attribute and converter/parameter)
            string sMissingTemplates = typeof(T) + Utilities.cNewLine;
            missingCellTemplates = validHeaders._Where(hdr => !dictPropertyCellTemplateMap.Keys._Contains(hdr)).ToObservableCollection();
            sMissingTemplates += missingCellTemplates.AppendText(", ");
            Clipboard.SetText(sMissingTemplates);
            MessageBoxUtil.Show(true, "Properties with missing cell templates\t\nhas been copied data to clipboard..\t\n\n" + sMissingTemplates);

            nameSortLast = "";
            UniversalDataSource = info.ToObservableCollection();
        }

        private void PopulateFilters()
        {
            Dictionary<string, string> allFilters = new Dictionary<string, string>();
            switch (SelectedDisplayGroup)
            {
                case eDisplayGroup.Hierarchical:
                case eDisplayGroup.Summary:
                    allFilters = new Dictionary<string, string>();
                    // throw new NotImplementedException("YET TO BE IMPLEMENTED.."); // RKD
                    break;

                case eDisplayGroup.Series:
                    allFilters = AttributeLists<SeriesInfo>.PropertyMapFilterable;
                    break;

                case eDisplayGroup.Season:
                    allFilters = AttributeLists<SeasonInfo>.PropertyMapFilterable;
                    break;

                case eDisplayGroup.Episode:
                default:
                    allFilters = AttributeLists<EpisodeInfo>.PropertyMapFilterable;
                    break;
            }

            AllFilters = allFilters;
            SelectedFilter = null;
        }

        private void PopulateFilterValues()
        {
            ObservableCollection<string> allFilterValues = new ObservableCollection<string>();
            if (!SelectedFilter.IsNullOrEmpty())
            {
                switch (SelectedDisplayGroup)
                {
                    case eDisplayGroup.Hierarchical:
                    case eDisplayGroup.Summary:
                        allFiltersValues = new ObservableCollection<string>();
                        break;

                    case eDisplayGroup.Series:
                        allFilterValues = AllSeriesInfo._Where(srs => srs.SeriesTitle == (SelectedSeries == DefaultAll ? srs.SeriesTitle : SelectedSeries))
                            ._Select(ep => Convert.ToString(ep.GetType().GetProperty(SelectedFilter).GetValue(ep, null))).ToObservableCollection();
                        break;

                    case eDisplayGroup.Season:
                        allFilterValues = AllSeasonInfo._Where(ssn => ssn.SeriesTitle == (SelectedSeries == DefaultAll ? ssn.SeriesTitle : SelectedSeries))
                            ._Select(ep => Convert.ToString(ep.GetType().GetProperty(SelectedFilter).GetValue(ep, null))).ToObservableCollection();
                        break;

                    case eDisplayGroup.Episode:
                    default:
                        allFilterValues = AllEpisodeInfo._Where(ep => ep.SeriesTitle == (SelectedSeries == DefaultAll ? ep.SeriesTitle : SelectedSeries))
                            ._Select(ep => Convert.ToString(ep.GetType().GetProperty(SelectedFilter).GetValue(ep, null))).ToObservableCollection();
                        break;
                }
            }

            AllFilterValues = allFilterValues.Distinct().AddDefaultItemAndSort(defaultNone)
                ._OrderBy(val => val.PadLeft(100)).ToObservableCollection();
        }

        private static List<object> IterateColumnsValues(Microsoft.Office.Interop.Excel.Worksheet worksheet, int iColMax, int iRow)
        {
            List<object> rowItems = new List<object>();
            for (int iCol = 1; iCol <= iColMax; iCol++) // cols
                rowItems.Add(worksheet.Application.Cells[iRow, iCol].Value);

            return rowItems;
        }

        private void PopulateSeasonInfo()
        {
            // RKD check_for_existing_first;

            // Create seasons
            ObservableCollection<SeasonInfo> allSeasons = AllEpisodeInfo
                .GroupBy(ep => new { SeriesTitle = ep.SeriesTitle, SeasonIndex = ep.SeasonIndex })
                .Select(ssn => ssn.Key).Distinct()._Select(grp =>
                {
                    SeasonInfo tmpSeasonInfo = new SeasonInfo { SeriesTitle = grp.SeriesTitle, SeasonIndex = grp.SeasonIndex };
                    tmpSeasonInfo.UpdatePadLength(minSeasonPadLength);

                    return tmpSeasonInfo;
                }).ToObservableCollection();

            // Populate episodes
            allSeasons._ForEach(ssn => ssn.Episodes = AllEpisodeInfo._Where(ep => ep.SeriesTitle == ssn.SeriesTitle && ep.SeasonIndex == ssn.SeasonIndex).ToObservableCollection());
            AllSeasonInfo = allSeasons;
        }

        private void PopulateSeriesInfo()
        {
            ObservableCollection<SeriesInfo> allSeriesInfo = AllSeriesBasePath;
            ObservableCollection<string> seriesTitles = AllSeasonInfo.GroupBy(ep => new { SeriesTitle = ep.SeriesTitle })
                .Select(ssn => ssn.Key).Distinct()._Select(grp => grp.SeriesTitle).ToObservableCollection();

            foreach (string title in seriesTitles)
            {
                if (!SeriesPathMap.Keys._Contains(title))
                {
                    SeriesPathMap.AddToDictionary(title, "");
                    allSeriesInfo.Add(new SeriesInfo { SeriesTitle = title });
                }
            }

            // Create series

            // Populate seasons
            allSeriesInfo._ForEach(srs => srs.Seasons = AllSeasonInfo._Where(ssn => ssn.SeriesTitle == srs.SeriesTitle).ToObservableCollection());
            AllSeriesInfo = allSeriesInfo;
        }

        private void WriteInfoXml<TIn, TOut>(ObservableCollection<TIn> data)
            where TIn : class, new()
            where TOut : class, new()
        {
            Dispatcher.CurrentDispatcher.BeginInvoke(new Action(() =>
            {
                try
                {
                    string sFileName = typeof(TIn).FullName + ".xml";
                    string sSeriesBasePath = ApplicationPath.AppendPath(sFileName);
                    string sSeriesBaseInfo = data._Select(item => Utilities.CopyValues<TIn, TOut>(item))
                        .ToObservableCollection().SerializeToFormattedData(eSerializationFormat.Xml)
                        .Replace("InfoMirror", "Info").RemoveText("T00:00:00");

                    if (File.Exists(sSeriesBasePath)) File.Delete(sSeriesBasePath);
                    Utilities.CreateFile(sSeriesBasePath);

                    File.WriteAllText(sSeriesBasePath, sSeriesBaseInfo, Encoding.Unicode);
                    // RKD : Utilities.OpenPath(sSeriesBasePath, eOpeningApp.Notepad);

                    //ObservableCollection<TOut> data = File.ReadAllText(sSeriesBasePath, Encoding.Unicode).Replace("Info", "InfoMirror")
                    //    .DeSerializeFromFormattedData<ObservableCollection<TOut>>(eSerializationFormat.Xml);
                }
                catch (Exception) { }
            }));
        }

        private string GetSeriesPathFromMap(string seriesTitle)
        {
            return SeriesPathMap.GetValueFromKey(seriesTitle).Coalesce().Trim();
        }

        private void DisplayStatus(DateTime start, DateTime end, string mode, string title)
        {
            AppStatus = Resources.Status_OperationCompleted.FormatTextFromParams(mode, (end - start).FormatTimeSpan());
            MessageBox.Show("{0}..\n\nDate:\t{1}\nStart:\t{2}\nEnd:\t{3}\nDuration:\t{4}"
                .FormatTextFromParams(mode, start.ToString("yyyy-MMM-dd"), start.ToString("HH:mm:ss.fff"), end.ToString("HH:mm:ss.fff"), (end - start).FormatTimeSpan()), title);
        }
    }

    public sealed class ExcelHelper
    {
        private const string CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FILENAME>;Extended Properties=\"Excel 8.0;HDR=Yes;\";";

        public static System.Data.DataTable GetDataTableFromExcelFile(string fullFileName, ref string sheetName)
        {
            OleDbConnection objConnection = new OleDbConnection();
            objConnection = new OleDbConnection(CONNECTION_STRING.Replace("<FILENAME>", fullFileName));
            DataSet dsImport = new DataSet();

            try
            {
                objConnection.Open();

                System.Data.DataTable dtSchema = objConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if ((null == dtSchema) || (dtSchema.Rows.Count <= 0))
                {
                    //raise exception if needed
                }

                if ((null != sheetName) && (0 != sheetName.Length))
                {
                    if (!CheckIfSheetNameExists(sheetName, dtSchema))
                    {
                        //raise exception if needed
                    }
                }
                else
                {
                    //Reading the first sheet name from the Excel file.
                    sheetName = Convert.ToString(dtSchema.Rows[0]["TABLE_NAME"]);
                }

                new OleDbDataAdapter("SELECT * FROM [" + sheetName + "]", objConnection).Fill(dsImport);
            }
            catch (Exception)
            {
                //raise exception if needed
            }
            finally
            {
                // Clean up.
                if (objConnection != null)
                {
                    objConnection.Close();
                    objConnection.Dispose();
                }
            }


            return dsImport.Tables[0];
            #region Commented code for importing data from CSV file.
            //				string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source=" + System.IO.Path.GetDirectoryName(fullFileName) +";" +"Extended Properties=\"Text;HDR=YES;FMT=Delimited\"";
            //
            //				System.Data.OleDb.OleDbConnection conText = new System.Data.OleDb.OleDbConnection(strConnectionString);
            //				new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + System.IO.Path.GetFileName(fullFileName).Replace(".", "#"), conText).Fill(dsImport);
            //				return dsImport.Tables[0];

            #endregion
        }

        /// <summary>
        /// This method checks if the user entered sheetName exists in the Schema Table
        /// </summary>
        /// <param name="sheetName">Sheet name to be verified</param>
        /// <param name="dtSchema">schema table </param>
        private static bool CheckIfSheetNameExists(string sheetName, System.Data.DataTable dtSchema)
        {
            foreach (DataRow dataRow in dtSchema.Rows)
            {
                if (sheetName == Convert.ToString(dataRow["TABLE_NAME"]))
                {
                    return true;
                }
            }
            return false;
        }
    }
}
